package com.yoooya.base.admin.utils;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

public class ExcelReadUtil {

    private XSSFWorkbook workbook;// 工作簿

    /**
     * 读取文件路径字符串。
     * <p>
     * 详细描述。
     * </p>
     *
     * @param strfile
     */
    public void importExcel(String strfile) {
        try {
            // 获取工作薄workbook
            workbook = new XSSFWorkbook(new FileInputStream(strfile));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取文件。
     * <p>
     * 详细描述。
     * </p>
     *
     * @param file
     */
    public void importExcel(File file) {
        try {
            workbook = new XSSFWorkbook(new FileInputStream(file));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取文件流。
     * <p>
     * 详细描述。
     * </p>
     *
     * @param filestream
     */
    public void importExcel(InputStream filestream) {
        try {
            workbook = new XSSFWorkbook(filestream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取想要第几张工作表第几行的数据importExcel导入。
     * <p>
     * 详细描述。
     * </p>
     *
     * @param sheetNumber
     * @return
     */
    @SuppressWarnings("deprecation")
    public List<Vector<Object>> readSet(int sheetNumber, int rowIndex) {
        List<Vector<Object>> result = new ArrayList<Vector<Object>>();
        try {
            // 获得指定的sheet
            XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
            // 获得sheet总行数
            int rowCount = sheet.getLastRowNum();
            if (rowCount < 1) {
                return result;
            }
            // HashMap<Integer, Object> map=new HashMap<Integer, Object>();
            // 遍历行row
            for (; rowIndex <= rowCount; rowIndex++) {
                // 获得行对象
                XSSFRow row = sheet.getRow(rowIndex);
                if (null != row) {
                    // List<Object> rowData = new ArrayList<Object>();
                    Vector<Object> vector = new Vector<Object>();
                    // 获得本行中单元格的个数
                    int cellCount = row.getLastCellNum();
                    // 遍历列cell
                    for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) {
                        XSSFCell cell = row.getCell(cellIndex);
                        // 获得指定单元格中的数据
                        Object cellStr = this.getCellString(cell);

                        // map.put(arg0, arg1)
                        vector.add(cellStr);
                    }
                    result.add(vector);
                }
            }
        } catch (Exception e) {
            System.out.println("readSet error:"+e.getMessage());
            result = null;
        }
        return result;
    }

    /**
     * 获取想要第几张工作表的数据importExcel导入。
     * <p>
     * 详细描述。
     * </p>
     *
     * @param sheetNumber
     * @return
     */
    @SuppressWarnings({"unchecked", "deprecation"})
    public List readSet(int sheetNumber) {
        List<List> result = new ArrayList<List>();
        // 获得指定的sheet
        XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
        // 获得sheet总行数
        int rowCount = sheet.getLastRowNum();
        if (rowCount < 1) {
            return result;
        }
        // HashMap<Integer, Object> map=new HashMap<Integer, Object>();
        // 遍历行row
        for (int rowIndex = 0; rowIndex <= rowCount; rowIndex++) {
            // 获得行对象
            XSSFRow row = sheet.getRow(rowIndex);
            if (null != row) {
                // List<Object> rowData = new ArrayList<Object>();
                Vector<Object> vector = new Vector<Object>();
                // 获得本行中单元格的个数
                int cellCount = row.getLastCellNum();
                // 遍历列cell
                for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) {
                    XSSFCell cell = row.getCell(cellIndex);
                    // 获得指定单元格中的数据
                    Object cellStr = this.getCellString(cell);

                    // map.put(arg0, arg1)
                    vector.add(cellStr);
                }
                result.add(vector);
            }
        }

        return result;
    }

    /**
     * 从第几张工作表第几行的数据importExcel导入。
     * <p>
     * 详细描述。
     * </p>
     *
     * @param sheetNumber
     * @param rowIndex
     * @return
     */
    @SuppressWarnings({"unchecked", "deprecation"})
    public List readRow(int sheetNumber, int rowIndex) {
        List result = new ArrayList();
        // 获得指定的sheet
        XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
        // 获得sheet总行数
        int rowCount = sheet.getLastRowNum();
        if (rowCount < 1) {
            return result;
        }
        // 遍历行row
        // for (int rowIndex = rows+2; rowIndex <= rowCount; rowIndex++) {
        // 获得行对象
        XSSFRow row = sheet.getRow(rowIndex);
        if (null != row) {
            // Vector<Object> vector=new Vector<Object>();
            // 获得本行中单元格的个数
            int cellCount = row.getLastCellNum();
            // 遍历列cell
            for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) {
                XSSFCell cell = row.getCell(cellIndex);
                // 获得指定单元格中的数据
                Object cellStr = this.getCellString(cell);
                // vector.add(cellStr);
                result.add(cellStr);
            }
        }
        // }

        return result;
    }

    /**
     * 获取指定工作表的总。
     * <p>
     * 详细描述。
     * </p>
     *
     * @param sheetNumber
     * @return
     */
    public int getRowIndex(int sheetNumber) {
        XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
        // 获得sheet总行数
        int rowCount = sheet.getLastRowNum();
        if (rowCount < 1) {
            return 0;
        }
        return rowCount;
    }

    /**
     * 从第几张工作表第几行读到第几行。
     * <p>
     * 详细描述。
     * </p>
     *
     * @param sheetNumber
     * @param rows
     * @param getrows
     * @return
     */
    @SuppressWarnings({"unchecked", "deprecation"})
    public List readCell(int sheetNumber, int rows, int getrows) {
        List<List> result = new ArrayList<List>();

        // 获得指定的sheet
        XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
        // 获得sheet总行数
        int rowCount = getrows;
        if (rowCount < 1) {
            return result;
        }
        // HashMap<Integer, Object> map=new HashMap<Integer, Object>();
        // 遍历行row
        for (int rowIndex = rows + 2; rowIndex <= rowCount; rowIndex++) {
            // 获得行对象
            XSSFRow row = sheet.getRow(rowIndex);
            if (null != row) {
                // List<Object> rowData = new ArrayList<Object>();
                Vector<Object> vector = new Vector<Object>();
                // 获得本行中单元格的个数
                int cellCount = row.getLastCellNum();
                // 遍历列cell
                for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) {
                    XSSFCell cell = row.getCell(cellIndex);
                    // 获得指定单元格中的数据
                    Object cellStr = this.getCellString(cell);
                    // map.put(arg0, arg1)
                    vector.add(cellStr);
                }
                result.add(vector);
            }
        }

        return result;
    }

    /**
     * 读取第几张工作表的第几列。
     * <p>
     * 详细描述。
     * </p>
     *
     * @param sheetNumber
     * @param cells
     * @return
     */
    @SuppressWarnings("unchecked")
    public List readColum(int sheetNumber, int cells) {
        List<List> result = new ArrayList<List>();

        // 获得指定的sheet
        XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
        // 获得sheet总行数
        int rowCount = sheet.getLastRowNum();
        if (rowCount < 1) {
            return result;
        }
        // HashMap<Integer, Object> map=new HashMap<Integer, Object>();
        // 遍历行row
        for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++) {
            // 获得行对象
            XSSFRow row = sheet.getRow(rowIndex);
            if (null != row) {
                // List<Object> rowData = new ArrayList<Object>();
                Vector<Object> vector = new Vector<Object>();
                // 获得本行中单元格的个数
                XSSFCell cell = row.getCell(cells);
                Object cellStr = this.getCellString(cell);
                vector.add(cellStr);
                result.add(vector);
            }
        }
        return result;
    }

    /**
     * 获取一个cell的数据类型
     *
     * @param cell
     * @return
     */
    private Object getCellString(XSSFCell cell) {
        Object result = null;
        if (cell != null) {
            // 单元格类型：Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
            int cellType = cell.getCellType();
            switch (cellType) {
                case HSSFCell.CELL_TYPE_STRING:
                    result = cell.getRichStringCellValue().getString();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        result = cell.getDateCellValue();
                    } else
                        result = cell.getNumericCellValue();
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    result = cell.getNumericCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    result = cell.getBooleanCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    result = null;
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    result = null;
                    break;
                default:
                    break;
            }
        }
        return result;
    }

    /**
     * 删除整行
     *
     * @param sheetNum
     * @param rowNum
     */
    public void deleteRow(int sheetNum, int rowNum) {
        XSSFSheet sheet = workbook.getSheetAt(sheetNum);
        int lastRowNum = sheet.getLastRowNum();
        if (rowNum >= 0 && rowNum < lastRowNum)
            sheet.shiftRows(rowNum + 1, lastRowNum, -1);//将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行
        if (rowNum == lastRowNum) {
            XSSFRow removingRow = sheet.getRow(rowNum);
            if (removingRow != null)
                sheet.removeRow(removingRow);
        }
    }

    /**
     * 在行最后添加一个单元格
     *
     * @param sheetNum
     * @param rowNum
     */
    public void addCell(int sheetNum, int rowNum, String content) {
        XSSFSheet sheet = workbook.getSheetAt(sheetNum);
        XSSFRow row = sheet.getRow(rowNum);
        XSSFCell cell = row.createCell(row.getLastCellNum());
        XSSFCellStyle style = row.getCell(0).getCellStyle();
        cell.setCellStyle(style);
        cell.setCellValue(content);
    }

    /**
     * 新增或者修改特定单元格
     *
     * @param sheetNum
     * @param rowNum
     * @param content
     */
    public void addModifyCell(int sheetNum, int rowNum, int columnNum, String content) {
        XSSFSheet sheet = workbook.getSheetAt(sheetNum);
        XSSFRow row = sheet.getRow(rowNum);
        XSSFCell cell = row.getCell(columnNum);
        if (row.getCell(columnNum) == null) {
            cell = row.createCell(columnNum);
            XSSFCellStyle style = row.getCell(0).getCellStyle();
            cell.setCellStyle(style);
        }
        cell.setCellValue(content);
    }

    public XSSFWorkbook getWorkbook() {
        return workbook;
    }

    public void setWorkbook(XSSFWorkbook workbook) {
        this.workbook = workbook;
    }
}
